# -*- coding: utf-8 -*-

import os
import datetime
import pymysql

conn = pymysql.connect(host='127.0.0.1', # host='127.0.0.1',  192.168.23.2
                       port=3306,
                       user='root',
                       passwd='123456',
                       db='xyDb',
                       charset='UTF8')
print("获得数据库连接:")
print(conn)
cursor = conn.cursor()

sql = " DROP TABLE IF EXISTS `xyTable`; "
cursor.execute( sql )
print("删除表 ok")

sql ="  CREATE TABLE  `xyTable`  (  `mId` int(100) NOT NULL AUTO_INCREMENT, " \
    "`mType` varchar(10) NOT NULL, \
    `mName` varchar(100) NOT NULL, \
    `mDir` varchar(100) NOT NULL,\
    `mDesc` varchar(1000) NOT NULL,\
    `mUrl1` varchar(1000) NOT NULL,\
    `mUrl2` varchar(1000) NOT NULL,\
    `mLrc` varchar(1000) NOT NULL, \
    `mVisiable` varchar(10) NOT NULL, \
    `mTime` varchar(100) NOT NULL, \
    PRIMARY KEY (`mId`)  \
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; "
cursor.execute( sql )
print("创建表 ok")

def getFileList(dir, fileList):
    newDir = dir
    if os.path.isfile(dir):         # 如果是文件则添加进 fileList
        fileList.append(dir)
    elif os.path.isdir(dir):
        for s in os.listdir(dir):   # 如果是文件夹
            newDir = os.path.join(dir, s)
            getFileList(newDir, fileList)
    return fileList

# dir = "D:\\xyData\\"
dir = "/mnt/aivin-fun-data/xyData/"
list = getFileList( dir, [])
print("扫描资料库 ok , 数据总量为："  , len(list))

for path in list:
    mType = os.path.basename(os.path.dirname(os.path.dirname(path)))
    mUrl1 = os.path.basename(path)
    mName = mUrl1.split(".")[0]
    mDir =  os.path.basename(os.path.dirname(path))
    mDesc =""
    mUrl2= ""
    mLrc =  mName+".lrc"
    mVisiable ="1"
    mTime = datetime.datetime.now().strftime('%Y-%m-%d')

    sql = " insert  into xyTable(  mType , mName, mDir , mDesc , mUrl1 , mUrl2 , mLrc , mVisiable , mTime )" \
          " values(  '%s','%s','%s','%s','%s','%s' ,'%s','%s','%s' ) " \
          % (mType, mName, mDir, mDesc, mUrl1, mUrl2, mLrc, mVisiable, mTime)
    cursor.execute(sql)
    newId = int(conn.insert_id())
    conn.commit()
    print("插入成功 id:  ", newId)

print("更新完毕 !!! ")
cursor.close()
conn.commit()
conn.close()